package br.com.fiap.trabalho.dao;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;

import br.com.fiap.trabalho.entity.Actor;
import br.com.fiap.trabalho.entity.Movie;

public class JDBCActorDAO implements ActorDAO {
	
	private Map<Integer, Actor> actorsMap = new HashMap<Integer, Actor>();
	
	public Actor createActor(Actor actor) {
		
		Connection connection = null;
		
		try {
			connection = JDBCConnectionManager.getConnection();
			
			connection.setAutoCommit(false);
			
			if (actor.getId() == null) {
				insertActor(actor, connection);
				
				if (actor.getMovies() != null && !actor.getMovies().isEmpty()) {
					for (Movie movie : actor.getMovies()) {
						
						new JDBCMovieDAO().createMovie(movie);
					}
				}
				
			} else {
				updateActor(actor, connection);
			}
			
			connection.commit();
			connection.setAutoCommit(true);
			
		} catch (Exception e) {
			try {
				connection.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
			actor = null;
		} finally {
			if (connection != null) {
				JDBCConnectionManager.closeConnection(connection);
			}
		}
		
		return actor;
		
	}

	public boolean deleteActor(Actor actor) {
		
		Connection connection = null;
		
		boolean deleted = false;
		
		try {
			connection = JDBCConnectionManager.getConnection();
			
			String sql = "delete from actor where id = ?";
			
			PreparedStatement ps = connection.prepareStatement(sql);
			
			ps.setInt(1, actor.getId());
			ps.execute();
			
			deleted = true;
			
			ps.close();
			
		} catch (Exception e) {
			e.printStackTrace();
			
		} finally {
			if (connection != null) {
				JDBCConnectionManager.closeConnection(connection);
			}
		}
		
		return deleted;
		
	}

	public List<Actor> selectActorByName(String name) {
		List<Actor> actors = new ArrayList<Actor>();
		Connection connection = null;
		
		try {
			
			connection = JDBCConnectionManager.getConnection();
			
			StringBuilder sql = new StringBuilder();
			sql.append(" select a.id as actor_id, a.fullName as actor_fullName, a.birthDate as actor_birthDate, m.id as movie_id, m.title as movie_title, m.year_movie as movie_year ");
			sql.append(" from actor a left join movie_actor ma on ma.actors_id = a.id ");
			sql.append(" left join movie m on ma.movies_id = m.id");
			sql.append(" where a.fullName like ? ");
			
			PreparedStatement ps = connection.prepareStatement(sql.toString());
			ps.setString(1, "%"+name+"%");
			
			ResultSet rs = ps.executeQuery();
			
			while(rs.next()) {
				Actor actor = getResutSetActor(rs);

				actors.add(actor);
			}
			
		} catch (Exception e) {
			e.printStackTrace();
			return null;
			
		} finally {
			if (connection != null) {
				JDBCConnectionManager.closeConnection(connection);
			}
		}
		
		return actors;
		
	}

	public List<Actor> selectActors(int numberOfElements) {
		List<Actor> actors = new ArrayList<Actor>();
		Connection connection = null;
		
		try {
			
			connection = JDBCConnectionManager.getConnection();
			
			StringBuilder sql = new StringBuilder();
			sql.append(" select a.id as actor_id, a.fullName as actor_fullName, a.birthDate as actor_birthDate");
			sql.append(" from actor a");
			
			PreparedStatement ps = connection.prepareStatement(sql.toString());
			
			ps.setMaxRows(numberOfElements);
			
			ResultSet rs = ps.executeQuery();
			
			while(rs.next()) {
				Actor actor = getResutSetActor(rs);

				actors.add(actor);
			}
			
		} catch (Exception e) {
			e.printStackTrace();
			return null;
			
		} finally {
			if (connection != null) {
				JDBCConnectionManager.closeConnection(connection);
			}
		}
		
		return actors;
		
	}
	
	public List<Actor> selectActorsWithMovie(int numberOfElements) {
		List<Actor> actors = new ArrayList<Actor>();
		Connection connection = null;
		
		try {
			
			connection = JDBCConnectionManager.getConnection();
			
			StringBuilder sql = new StringBuilder();
			sql.append(" select a.id as actor_id, a.fullName as actor_fullName, a.birthDate as actor_birthDate, m.id as movie_id, m.title as movie_title, m.year_movie as movie_year ");
			sql.append(" from actor a inner join movie_actor ma on ma.actors_id = a.id ");
			sql.append(" inner join movie m on ma.movies_id = m.id");
			
			PreparedStatement ps = connection.prepareStatement(sql.toString());
			
			ps.setMaxRows(numberOfElements);
			
			ResultSet rs = ps.executeQuery();
			
			while(rs.next()) {
				Actor actor = getResutSetActor(rs);

				actors.add(actor);
			}
			
		} catch (Exception e) {
			e.printStackTrace();
			return null;
			
		} finally {
			if (connection != null) {
				JDBCConnectionManager.closeConnection(connection);
			}
		}
		
		return actors;
	}	
	
	public List<Actor> selectActorByAge(Integer age) {
		List<Actor> actors = new ArrayList<Actor>();
		Connection connection = null;
		
		try {
			
			connection = JDBCConnectionManager.getConnection();
			
			StringBuilder sql = new StringBuilder();
			sql.append(" select a.id as actor_id, a.fullName as actor_fullName, a.birthDate as actor_birthDate, m.id as movie_id, m.title as movie_title, m.year_movie as movie_year ");
			sql.append(" from actor a left join movie_actor ma on ma.actors_id = a.id ");
			sql.append(" left join movie m on ma.movies_id = m.id");
			sql.append(" where a.birthDate between ? and ? ");
			
			PreparedStatement ps = connection.prepareStatement(sql.toString());
			
			Calendar dataInic = Calendar.getInstance();
			dataInic.add(Calendar.YEAR, -(age + 1));
			
			Calendar dataFim = Calendar.getInstance();
			dataFim.add(Calendar.YEAR, -age);
			
			ps.setDate(1, new Date(dataInic.getTimeInMillis()));
			ps.setDate(2, new Date(dataFim.getTimeInMillis()));
			
			ResultSet rs = ps.executeQuery();
			
			while(rs.next()) {
				Actor actor = getResutSetActor(rs);

				actors.add(actor);
			}
			
		} catch (Exception e) {
			e.printStackTrace();
			return null;
			
		} finally {
			if (connection != null) {
				JDBCConnectionManager.closeConnection(connection);
			}
		}
		
		return actors;
		
	}

	public List<Actor> selectActorsByMovie(Movie movie) {
		
		if (movie == null) {
			return null;
		}
		
		List<Actor> actors = new ArrayList<Actor>();
		Connection connection = null;
		
		try {
			
			connection = JDBCConnectionManager.getConnection();
			
			StringBuilder sql = new StringBuilder();
			
			sql.append(" select a.id as actor_id, a.fullName as actor_fullName, a.birthDate as actor_birthDate, m.id as movie_id, m.title as movie_title, m.year_movie as movie_year ");
			sql.append(" from actor a left join movie_actor ma on ma.actors_id = a.id ");
			sql.append(" left join movie m on ma.movies_id = m.id");
			sql.append(" where ma.movies_id = ? ");
			
			PreparedStatement ps = connection.prepareStatement(sql.toString());
			ps.setInt(1, movie.getId());

			ResultSet rs = ps.executeQuery();
			
			while(rs.next()) {
				Actor actor = getResutSetActor(rs);

				actors.add(actor);
			}
			
		} catch (Exception e) {
			e.printStackTrace();
			return null;
			
		} finally {
			if (connection != null) {
				JDBCConnectionManager.closeConnection(connection);
			}
		}
		
		return actors;
		
	}
	
	private void insertActor(Actor actor, Connection connection)
			throws SQLException {
		String sql = "insert into actor (fullName, birthDate) values (?,?)";
		
		PreparedStatement ps = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
		ps.setString(1, actor.getFullName());
		ps.setDate(2, new Date(actor.getBirthDate().getTime()));
		
		ps.executeUpdate();
		
		setIdOnEntity(actor, ps);
		
		ps.close();
	}
	
	private void updateActor(Actor actor, Connection connection)
			throws Exception {
		
		String selectById = "select * from actor where id = ?";
		
		PreparedStatement ps = connection.prepareStatement(selectById);
		ps.setInt(1, actor.getId());
		
		ResultSet rs = ps.executeQuery();

		Actor persistedActor = null;
		if (rs.next()) {
			persistedActor = new Actor();
			
			persistedActor.setId(rs.getInt("id"));
			persistedActor.setBirthDate(rs.getDate("birthDate"));
			persistedActor.setFullName(rs.getString("fullName"));
		}
		
		if (persistedActor == null) {
			throw new Exception("No Actor found with id = " + actor.getId());
		}
			
		String sql = "update actor set fullName = ?, birthDate = ?";
		
		ps = connection.prepareStatement(sql);
		ps.setString(1, actor.getFullName());
		ps.setDate(2, new Date(actor.getBirthDate().getTime()));
		
		ps.executeUpdate();
		
		ps.close();
	}
	
	private Actor getResutSetActor(ResultSet rs) throws SQLException {
		
		Actor cachedActor = actorsMap.get(rs.getInt("actor_id"));
		
		if (cachedActor == null) {
			cachedActor = new Actor();
			
			cachedActor.setId(rs.getInt("actor_id"));
			cachedActor.setBirthDate(rs.getDate("actor_birthDate"));
			cachedActor.setFullName(rs.getString("actor_fullName"));
			
			try{
				rs.getInt("movie_id");
			}catch(SQLException sqe){
				return cachedActor;
			}
			
			actorsMap.put(cachedActor.getId(), cachedActor);
		}
		
		List<Movie> movies = cachedActor.getMovies();
		
		Integer idMovie = rs.getInt("movie_id");
		
		if (idMovie != 0 && movies == null) {
			movies = new ArrayList<Movie>();
			cachedActor.setMovies(movies);
			
			Movie movie = new Movie();
			movie.setId(rs.getInt("movie_id"));
			movie.setTitle(rs.getString("movie_title"));
			movie.setYear(rs.getInt("movie_year"));
			
			movies.add(movie);
		}
		
		return cachedActor;
	}
	
	private void setIdOnEntity(Actor actor, PreparedStatement ps) throws SQLException {
		
		ResultSet generatedKeys = ps.getGeneratedKeys();
		if (generatedKeys.next()) {
		    actor.setId(generatedKeys.getInt(1));
		}
		
	}
}
